package com.workspace.chb_website.mapper;

import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Mapper
@Repository("workLogMapper")
public interface WorkLogMapper {
    @Select("select to_char(startdate, 'yyyy-MM-dd') startdate, to_char(enddate, 'yyyy-MM-dd') enddate, projectname " +
            "from worklog,project " +
            "where project.project_id = worklog.project_id " +
            "and user_id = #{id} " +
            "and to_char(startdate, 'yyyy-mm') = #{month} " +
            "order by startdate")
    List<Map<String, String>> getAllWorkLog(String id, String month);

    @Select("select to_char(min(startdate), 'yyyy-mm') startmonth, to_char(max(startdate), 'yyyy-mm') endmonth " +
            "from worklog,users " +
            "where worklog.user_id = users.user_id and realname = #{name}")
    List<Map<String, String>> getWorkDate(String name);

    @Select("select to_char(w.startdate, 'dd') startday, to_char(w.enddate, 'dd') endday, p.projectname " +
            "from worklog w, project p, users u " +
            "where w.project_id = p.project_id " +
            "and w.user_id = u.user_id " +
            "and u.realname = #{name} " +
            "and to_char(w.startdate, 'yyyy-mm') = #{month}")
    List<Map<String, String>> getWorkLog(String name, String month);

    @Select("select distinct p.projectname " +
            "from worklog w, project p, users u " +
            "where w.project_id = p.project_id " +
            "and w.user_id = u.user_id " +
            "and u.realname = #{name} " +
            "and to_char(w.startdate, 'yyyy-mm') = #{month} " +
            "and p.projectname != '休息'")
    List<String> getProject(String name, String month);

    @Select("select projectname from project order by project_id")
    List<String> getAllProject();

    @Select("select projectname from project where projectname like '%${para}%'")
    List<String> findProject(String para);

    @Select("Select project_id from project where projectname = #{name}")
    String getIdByProject(String name);

    @Insert("insert into worklog(startdate, enddate, user_id, project_id) " +
            "values(to_date(#{startdate}, 'yyyy-mm-dd'), to_date(#{enddate}, 'yyyy-mm-dd'), #{id}, #{proid})")
    int addWorkLog(String id, String startdate, String enddate, int proid);

    @Insert("insert into project(projectname) values(#{project})")
    void addProject(String project);

    @Select("select count(*) from worklog where user_id = #{id} " +
            "and (to_date(#{startdate}, 'yyyy-mm-dd'),to_date(#{enddate}, 'yyyy-mm-dd')+1) overlaps (startdate, enddate)")
    int checkDate(String id, String startdate, String enddate);

    @Delete("delete from worklog where user_id = #{id} " +
            "and startdate = to_date(#{startdate}, 'yyyy-mm-dd') " +
            "and enddate = to_date(#{enddate}, 'yyyy-mm-dd')")
    int deleteWorkLog(String id, String startdate, String enddate);

    @Update("update worklog set project_id = #{proid} where user_id = #{id} " +
            "and startdate = to_date(#{startdate}, 'yyyy-mm-dd') " +
            "and enddate = to_date(#{enddate}, 'yyyy-mm-dd')")
    int updateWorkLog(String id, String startdate, String enddate, int proid);

    @Select("select count(*) from worklog where user_id = #{id}")
    int isHasLog(String id);
}
